package com.app.TCES.mapper;

import java.util.List;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import com.app.TCES.domain.Class;
import com.app.TCES.domain.Class_Course;
import com.app.TCES.domain.Course;
import com.app.TCES.domain.Department;
import com.app.TCES.domain.Teacher_Course;

@Mapper
public interface A_Class_CourseMapper {

	//查询班级课程信息
	@Select("select * from teacher t,class cl,course co,class_course cc where cl.classId=cc.classId and co.courseId=cc.courseId and t.tchId=cc.tchId")
	List<Class_Course> selectClass_Courses();
	
	//院系信息
	@Select("select * from department")
	List<Department> selectDept();
	
	//查询班级信息
	@Select("select * from class where deptId=#{deptId}")
	List<Class> selectClasses(@Param("deptId")int deptId);
	
	//查询课程信息
	@Select("select * from course")
	List<Course> selectCourses();
	
	//添加班级课程
	@Insert("insert into class_course(classId,courseId,tchId) values(#{classId},#{courseId},#{tchId})")
	void insertCC(@Param("classId")int classId,@Param("courseId")int courseId,@Param("tchId")int tchId);
	
	
	//查找此班级课程是否存在
	@Select("select count(*) from class_course where classId=#{classId} and courseId=#{courseId}")
	int selectTol(@Param("classId")int classId,@Param("courseId")int courseId);
	
	//删除class_course里的信息
	//1.查找class_course的ID
	@Select("select class_course_id from class_course where classId=#{classId} and courseId=#{courseId} and tchId=#{tchId}")
	int selectCCid(@Param("classId")int classId,@Param("courseId")int courseId,@Param("tchId")int tchId);
	//2.删除
	@Delete("delete from class_course where class_course_id=#{class_course_id}")
	void deleteCC(@Param("class_course_id")int class_course_id);
	
	//删除teacher_course里的信息
	//1.查找teacher_course的ID
	@Select("select teacher_course_id from teacher_course where tchId=#{tchId} and courseId=#{courseId}")
	int selectTCid(@Param("tchId")int tchId,@Param("courseId")int courseId);
	
	
	//2.删除(教师课程表)
	@Delete("delete from teacher_course where teacher_course_id=#{teacher_course_id}")
	void deleteTC(@Param("teacher_course_id")int teacher_course_id);
	
	//通过院系ID和课程ID查找相应教师
	@Select("select * from teacher_course tc,teacher t,department d where t.tchId=tc.tchId and tc.courseId=#{courseId} and t.deptId= d.deptId AND d.deptId=#{deptId}")
	List<Teacher_Course> selectC_T(@Param("courseId")int courseId,@Param("deptId")int deptId);
	
	//查找班级信息(条件:deptName)
	@Select("select * from class c,department d where c.deptId=d.deptId and d.deptName=#{deptName}")
	List<Class> selectClass(@Param("deptName")String deptName);
	
	
	//点击院系按钮查找信息(条件:deptName)
	@Select("select * from teacher t,class cl,course co,class_course cc,department d where t.tchId=cc.tchId and t.deptId=d.deptId and cl.classId=cc.classId and co.courseId=cc.courseId and d.deptName=#{deptName}")
	List<Class_Course> selectMsgBy_deptName(@Param("deptName")String deptName);
	
	//点击班级按钮查找信息(条件:className)
	@Select("select * from teacher t,class cl,course co,class_course cc,department d where t.tchId=cc.tchId and cl.classId=cc.classId and co.courseId=cc.courseId and t.deptId=d.deptId and cl.className=#{className} and d.deptName=#{deptName}")
	List<Class_Course> selectMsgBy_className(@Param("className")String className,@Param("deptName")String deptName);
}
